package com.examsys.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Paper;
import com.examsys.po.PaperSection;

/**
 * 试卷中的章节数据访问层实现类
 * @author edu-1
 *
 */
public class PaperSectionDaoImpl extends AbstractBaseDao<PaperSection, Integer> implements PaperSectionDao {

	/**
	 * 添加试卷中的章节
	 */
	@Override
	public void add(PaperSection obj) throws Exception {
		//构造插入语句
		String sql="Insert into PAPER_SECTION (ID,PAPER_ID,SECTION_NAME,PER_SCORE,REMARK) values (PAPER_SECTION_ID_SEQ.nextval,?,?,?,?)";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getSection_name(),obj.getPer_score(),obj.getRemark()});
	}

	/**
	 * 更新试卷中的章节
	 */
	@Override
	public void update(PaperSection obj) throws Exception {
		//构造更新语句
		String sql="UPDATE PAPER_SECTION SET PAPER_ID=?,SECTION_NAME=?,PER_SCORE=?,REMARK=? WHERE ID=?";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getSection_name(),obj.getPer_score(),obj.getRemark(),obj.getId()});
	}

	/**
	 * 删除试卷中的章节
	 * @param id 编号
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM PAPER_SECTION WHERE ID=?";
		this.execute(sql, new Object[]{id});	
	}

	/**
	 * 通过试卷编号删除章节
	 */
	public void deleteByPaperId(Integer id) throws Exception{
		//构建删除语句
		String sql="DELETE FROM PAPER_SECTION WHERE PAPER_ID=?";
		this.execute(sql, new Object[]{id});	
	}
	
	
	/**
	 * 试卷中的章节
	 * @param id 编号
	 */
	@Override
	public PaperSection get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.SECTION_NAME,a.PER_SCORE,a.REMARK,b.PAPER_NAME FROM PAPER_SECTION a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		Integer per_score = (Integer)m.get("PER_SCORE");//本章节的分数
		String section_name = (String)m.get("SECTION_NAME");//章节名称
		String remark = (String)m.get("REMARK");//备注
		
		Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
		String paper_name = (String)m.get("PAPER_NAME");//试卷名称
		
		PaperSection paperSection=new PaperSection();//创建实体类对象
		paperSection.setId(idd);
		paperSection.setPer_score(per_score);
		paperSection.setSection_name(section_name);
		paperSection.setRemark(remark);
		
		Paper paper=new Paper();//创建实体类对象
		paper.setId(paper_id);
		paper.setPaper_name(paper_name);
		
		paperSection.setPaper(paper);//设置关联对象
		
		return paperSection;
	}

	/**
	 * 获取所有试卷中的章节
	 */
	@Override
	public List<PaperSection> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.SECTION_NAME,a.PER_SCORE,a.REMARK,b.PAPER_NAME FROM PAPER_SECTION a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<PaperSection> list=new ArrayList<PaperSection>();//存放PaperSection类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer per_score = (Integer)m.get("PER_SCORE");//本章节的分数
			String section_name = (String)m.get("SECTION_NAME");//章节名称
			String remark = (String)m.get("REMARK");//备注
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			PaperSection paperSection=new PaperSection();//创建实体类对象
			paperSection.setId(idd);
			paperSection.setPer_score(per_score);
			paperSection.setSection_name(section_name);
			paperSection.setRemark(remark);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			paperSection.setPaper(paper);//设置关联对象
			list.add(paperSection);
		}
		return list;
	}

	/**
	 * 带条件获取试卷中的章节
	 */
	@Override
	public List<PaperSection> getList(PaperSection obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.SECTION_NAME,a.PER_SCORE,a.REMARK,b.PAPER_NAME FROM PAPER_SECTION a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getSection_name()!=null&&!"".equals(obj.getSection_name())){
				sql+=" AND a.SECTION_NAME='"+obj.getSection_name()+"'";
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getId()!=null
					&&obj.getPaper().getId()!=0){
				sql+=" AND a.PAPER_ID="+obj.getPaper().getId();
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getPaper_name()!=null
					&&!"".equals(obj.getPaper().getPaper_name())){
				sql+=" AND a.PAPER_NAME='"+obj.getPaper().getPaper_name()+"'";
			}
			
		}

		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<PaperSection> list=new ArrayList<PaperSection>();//存放PaperSection类型对象的集合
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer per_score = (Integer)m.get("PER_SCORE");//本章节的分数
			String section_name = (String)m.get("SECTION_NAME");//章节名称
			String remark = (String)m.get("REMARK");//备注
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			PaperSection paperSection=new PaperSection();//创建实体类对象
			paperSection.setId(idd);
			paperSection.setPer_score(per_score);
			paperSection.setSection_name(section_name);
			paperSection.setRemark(remark);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			paperSection.setPaper(paper);//设置关联对象
			list.add(paperSection);
		}
		return list;
	}

}
